package com.example.demo.mapper;

import com.example.demo.entity.TeamMember;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

@Mapper
public interface TeamMemberMapper {

    // 根据团队ID查找成员
    @Select("SELECT * FROM team_members WHERE team_id = #{teamId}")
    List<TeamMember> findByTeamId(@Param("teamId") Long teamId);

    // 根据学生ID查找团队关系
    @Select("SELECT * FROM team_members WHERE student_id = #{studentId}")
    List<TeamMember> findByStudentId(@Param("studentId") Long studentId);

    // 查找特定学生在特定团队中的角色
    @Select("SELECT * FROM team_members WHERE team_id = #{teamId} AND student_id = #{studentId}")
    Optional<TeamMember> findByTeamIdAndStudentId(@Param("teamId") Long teamId, @Param("studentId") Long studentId);

    // 统计团队成员数量
    @Select("SELECT COUNT(*) FROM team_members WHERE team_id = #{teamId}")
    long countByTeamId(@Param("teamId") Long teamId);

    // 查找团队的队长
    @Select("SELECT * FROM team_members WHERE team_id = #{teamId} AND role = 'LEADER'")
    Optional<TeamMember> findLeaderByTeamId(@Param("teamId") Long teamId);

    // 查找团队中的普通成员
    @Select("SELECT * FROM team_members WHERE team_id = #{teamId} AND role = 'MEMBER'")
    List<TeamMember> findMembersByTeamId(@Param("teamId") Long teamId);

    // 检查学生是否在团队中
    @Select("SELECT COUNT(*) > 0 FROM team_members WHERE team_id = #{teamId} AND student_id = #{studentId}")
    boolean existsByTeamIdAndStudentId(@Param("teamId") Long teamId, @Param("studentId") Long studentId);

    // 检查学生是否是团队的队长
    @Select("SELECT COUNT(*) > 0 FROM team_members WHERE team_id = #{teamId} AND student_id = #{studentId} AND role = 'LEADER'")
    boolean isTeamLeader(@Param("teamId") Long teamId, @Param("studentId") Long studentId);

    // 根据团队ID列表查找团队成员
    @Select({
            "<script>",
            "SELECT * FROM team_members WHERE team_id IN",
            "<foreach collection='teamIds' item='teamId' open='(' separator=',' close=')'>",
            "#{teamId}",
            "</foreach>",
            "</script>"
    })
    List<TeamMember> findByTeamIdIn(@Param("teamIds") List<Long> teamIds);

    // 根据学生ID列表查找团队关系
    @Select({
            "<script>",
            "SELECT * FROM team_members WHERE student_id IN",
            "<foreach collection='studentIds' item='studentId' open='(' separator=',' close=')'>",
            "#{studentId}",
            "</foreach>",
            "</script>"
    })
    List<TeamMember> findByStudentIdIn(@Param("studentIds") List<Long> studentIds);

    // 删除团队成员
    @Delete("DELETE FROM team_members WHERE team_id = #{teamId} AND student_id = #{studentId}")
    void deleteByTeamIdAndStudentId(@Param("teamId") Long teamId, @Param("studentId") Long studentId);

    // 删除团队的所有成员
    @Delete("DELETE FROM team_members WHERE team_id = #{teamId}")
    void deleteByTeamId(@Param("teamId") Long teamId);

    // 插入团队成员
    @Insert("INSERT INTO team_members (team_id, student_id, role, join_date) " +
            "VALUES (#{teamId}, #{studentId}, #{role}, #{joinDate})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(TeamMember teamMember);

    // 更新团队成员
    @Update("UPDATE team_members SET role = #{role} WHERE id = #{id}")
    int update(TeamMember teamMember);

    // 查找学生在特定课程中的所有团队
    @Select("SELECT tm.* FROM team_members tm JOIN teams t ON tm.team_id = t.id WHERE tm.student_id = #{studentId} AND t.course_id = #{courseId}")
    List<TeamMember> findByStudentIdAndCourseId(@Param("studentId") Long studentId, @Param("courseId") Long courseId);
}